'''
Created on Oct 8, 2012

@author: peng
'''
import os
import MySQLdb


BELUGA_DB_CONFIG = {'host': '10.42.133.171',
                    'user': 'dev_api',
                    'password': 'api_dev_pwd',
                    'db': 'beluga_api',
                    'port': 3306}


class MySQLdbWrapper:

    conn = None

    def connect(self):
        self.conn = MySQLdb.connect(host=BELUGA_DB_CONFIG['host'],
                                    user=BELUGA_DB_CONFIG['user'],
                                    passwd=BELUGA_DB_CONFIG['password'],
                                    db=BELUGA_DB_CONFIG['db'],
                                    port=BELUGA_DB_CONFIG['port'])
        self.conn.set_character_set('utf8')
        self.conn.autocommit(True)

    def cursor(self):
        try:
            self.connect()
            return self.conn.cursor()
        except MySQLdb.OperationalError:
            self.connect()
            return self.conn.cursor()

_db = MySQLdbWrapper()


def get_data(sql):
    try:
        cursor = _db.cursor()
        cursor.execute(sql)
        data = cursor.fetchall()
        return data
    finally:
        cursor.close()

path = '/mnt/export/user-data'

# user daily
sql_0 = \
'''
select app_key, date, promotion_id, app_version, channel,
active_count, upgrade_count, launch_times, use_time
from user_daily
where date<'2012-10-01'
'''

# country_daily
sql_1 = \
'''
select app_key, date, promotion_id, app_version, channel, country,
active_count, upgrade_count, launch_times, use_time
from country_daily
where date<'2012-10-01'
'''

# os_version_daily
sql_2 = \
'''
select app_key, date, promotion_id, app_version, channel, os_version,
active_count, upgrade_count, launch_times, use_time
from os_version_daily
where date<'2012-10-01'
'''

# language_daily
sql_3 = \
'''
select app_key, date, promotion_id, app_version, channel, language,
active_count, upgrade_count, launch_times, use_time
from language_daily
where date<'2012-10-01'
'''

# resolution_daily
sql_4 = \
'''
select app_key, date, promotion_id, app_version, channel, resolution,
active_count, upgrade_count, launch_times, use_time
from resolution_daily
where date<'2012-10-01'
'''

# access_daily
sql_5 = \
'''
select app_key, date, promotion_id, app_version, channel, access,
active_count, upgrade_count, launch_times, use_time
from access_daily
where date<'2012-10-01'
'''

# carrier_daily
sql_6 = \
'''
select app_key, date, promotion_id, app_version, channel, carrier,
active_count, upgrade_count, launch_times, use_time
from carrier_daily
where date<'2012-10-01'
limit 100000 offset %s
'''

# device_model_daily
sql_7 = \
'''
select app_key, date, promotion_id, app_version, channel, device_model,
new_count, upgrade_count, launch_times, use_time
from device_model_daily
where date<'2012-10-01'
limit 100000 offset %s
'''


def remove_control_characters(line):
    # mpa = dict.fromkeys(range(32))
    # line = line.translate(mpa)
    return line


def flush(data_list):
    if len(data_list) == 0:
        return
    f = open(os.path.join(path, 'active_user_data'), 'a+')
    data = '\n'.join(data_list)
    f.write(data)
    f.write('\n')
    f.close()


def export_summary():
    data_list = []
    rows = get_data(sql_0)
    for row in rows:
        line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                          '0', '-', 'active_count:' + str(row[5]), \
                                    'upgrade_count:' + str(row[6]), \
                                    'launch_times:' + str(row[7]), \
                                    'use_time:' + str(row[8])])
        data_list.append(remove_control_characters(line))
    flush(data_list)


def export_country():
    data_list = []
    rows = get_data(sql_1)
    for row in rows:
        line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                          '1', row[5], 'active_count:' + str(row[6]), \
                                       'upgrade_count:' + str(row[7]), \
                                       'launch_times:' + str(row[8]), \
                                       'use_time:' + str(row[9])])
        data_list.append(remove_control_characters(line))
    flush(data_list)


def export_os_version():
    data_list = []
    rows = get_data(sql_2)
    for row in rows:
        line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                          '2', row[5], 'active_count:' + str(row[6]), \
                                       'upgrade_count:' + str(row[7]), \
                                       'launch_times:' + str(row[8]), \
                                       'use_time:' + str(row[9])])
        data_list.append(remove_control_characters(line))
    flush(data_list)


def export_language():
    data_list = []
    rows = get_data(sql_3)
    for row in rows:
        line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                          '3', row[5], 'active_count:' + str(row[6]), \
                                       'upgrade_count:' + str(row[7]), \
                                       'launch_times:' + str(row[8]), \
                                       'use_time:' + str(row[9])])
        data_list.append(remove_control_characters(line))
    flush(data_list)


def export_resolution():
    data_list = []
    rows = get_data(sql_4)
    for row in rows:
        line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                          '4', row[5], 'active_count:' + str(row[6]), \
                                       'upgrade_count:' + str(row[7]), \
                                       'launch_times:' + str(row[8]), \
                                       'use_time:' + str(row[9])])
        data_list.append(remove_control_characters(line))
    flush(data_list)


def export_access():
    data_list = []
    rows = get_data(sql_5)
    for row in rows:
        line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                          '5', row[5], 'active_count:' + str(row[6]), \
                                       'upgrade_count:' + str(row[7]), \
                                       'launch_times:' + str(row[8]), \
                                       'use_time:' + str(row[9])])
        data_list.append(remove_control_characters(line))
    flush(data_list)


def export_carrier():
    offset = 0
    while True:
        print offset
        data_list = []
        rows = get_data(sql_6 % offset)
        if len(rows) == 0:
            break
        for row in rows:
            line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                              '6', row[5], 'active_count:' + str(row[6]), \
                                           'upgrade_count:' + str(row[7]), \
                                           'launch_times:' + str(row[8]), \
                                           'use_time:' + str(row[9])])
            data_list.append(remove_control_characters(line))
        flush(data_list)
        offset += 100000


def export_device_model():
    offset = 0
    while True:
        print offset
        data_list = []
        rows = get_data(sql_7 % offset)
        if len(rows) == 0:
            break

        for row in rows:
            line = '\t'.join([row[0], str(row[1])[:10].replace('-', ''), row[2], row[3], row[4], \
                              '7', row[5], 'active_count:' + str(row[6]), \
                                           'upgrade_count:' + str(row[7]), \
                                           'launch_times:' + str(row[8]), \
                                           'use_time:' + str(row[9])])
            data_list.append(remove_control_characters(line))
        flush(data_list)
        offset += 100000


if __name__ == '__main__':
    export_summary()
    print 'summary done'
    export_country()
    print 'export_country done'
    export_os_version()
    print 'export_os_version done'
    export_language()
    print 'export_language done'
    export_resolution()
    print 'export_resolution done'
    export_access()
    print 'export_access done'
    export_carrier()
    print 'export_carrier done'
    export_device_model()
    print 'export_device_model done'
